package chances.declare.repo;

import java.text.ParseException;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceUnit;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import chances.commons.crypto.DateUtils;
import chances.declare.util.DateUtil;
import chances.declare.web.MergerModel;
import chances.declare.web.UnitContentCPContent;

/**
*@Author Guoyh
*@Date 2017年7月26日 下午5:25:19
*chances.declare.repo
*/
public class UnitContentRepositoryImpl {

    @PersistenceUnit
    private EntityManagerFactory emf;

    private final String SQL_UNIT_CP = "select new chances.declare.web.UnitContentCPContent(c,u) from CPContent c left join UnitContent u on u.id=c.unitContentId where (c.status = 1 or c.status = 2 ) ";
    private final String SQL_UNIT_CP_ADOPT = "select new chances.declare.web.UnitContentCPContent(c,u) from CPContent c,UnitContent u where c.unitContentId=u.id";

    // 内容归并页面和查询申报页面中的查询
    @SuppressWarnings("unchecked")
    public Page<UnitContentCPContent> search(MergerModel model, Pageable pageable)
            throws Exception {
        EntityManager em = emf.createEntityManager();
        Query cq = this.getQuery(model, em, SQL_UNIT_CP);

        List<UnitContentCPContent> sql_count = cq.getResultList();

        List<UnitContentCPContent> sql_list = cq
                .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
                .setMaxResults(pageable.getPageSize()).getResultList();
        em.close();
        Page<UnitContentCPContent> pageImpl = new PageImpl<UnitContentCPContent>(sql_list, pageable,
                sql_count.size());
        return pageImpl;
    }

    // 获取Excel表格类型数据
    @SuppressWarnings("unchecked")
    public List<UnitContentCPContent> exportExcel(MergerModel model, Pageable pageable)
            throws Exception {
        EntityManager em = emf.createEntityManager();
        Query cq = this.getQuery(model, em, SQL_UNIT_CP);
        List<UnitContentCPContent> sql_count = cq.getResultList();
        return sql_count;
    }

    // 采纳页面的查询
    @SuppressWarnings("unchecked")
    public Page<UnitContentCPContent> adoptDeclareSearch(MergerModel model, Pageable pageable)
            throws Exception {
        EntityManager em = emf.createEntityManager();
        Query cq = this.getQuery(model, em, SQL_UNIT_CP_ADOPT);
        List<UnitContentCPContent> sql_count = cq.getResultList();
        List<UnitContentCPContent> sql_list = cq
                .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
                .setMaxResults(pageable.getPageSize()).getResultList();
        em.close();
        Page<UnitContentCPContent> pageImpl = new PageImpl<UnitContentCPContent>(sql_list, pageable,
                sql_count.size());
        return pageImpl;
    }

    public Page<UnitContentCPContent> getUnitContentCPContent(Pageable pageable) {
        EntityManager em = emf.createEntityManager();
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<UnitContentCPContent> cq = cb.createQuery(UnitContentCPContent.class);
        Root<UnitContentCPContent> root = cq.from(UnitContentCPContent.class);

        List<UnitContentCPContent> sql_count = em.createQuery(cq).getResultList();

        List<UnitContentCPContent> sql_list = em.createQuery(cq)
                .setFirstResult(pageable.getPageNumber() * pageable.getPageSize())
                .setMaxResults(pageable.getPageSize()).getResultList();
        em.close();

        Page<UnitContentCPContent> pageImpl = new PageImpl<UnitContentCPContent>(sql_list, pageable,
                sql_count.size());
        return pageImpl;
    }

    // 用于处理组合组合查询的条件
    private Query getQuery(MergerModel model, EntityManager em, String sql) throws Exception {
        StringBuffer hql = new StringBuffer();
        hql.append(sql);
        if (model != null) {
            // 片名
            if (StringUtils.isNotBlank(model.getCpName())) {
                hql.append(" and c.name like :cpName");
            }
            // 归并名称
            if (StringUtils.isNotBlank(model.getName())) {
                hql.append(" and u.name like :name");
            }
            // cp的ID
            if (model.getOrgId() != null) {
                hql.append(" and c.orgId = :orgId");
            }
            // 内容类型
            if (StringUtils.isNotBlank(model.getContentType())) {
                hql.append(" and c.contentType = :contentType");
            }
            // 是否独家
            if (model.getExclusive() != null) {
                hql.append(" and c.exclusive = :exclusive");
            }
            // 是否采纳
            if (model.getIsAdopted() != null) {
                hql.append(" and c.isAdopted = :isAdopted");
            }
            // 申报的开始时间
            if (StringUtils.isNotBlank(model.getStartDeclareTime())) {
                hql.append(" and c.declareTime >= :startDeclareTime");
            }
            // 申报的结束时间
            if (StringUtils.isNotBlank(model.getEndDecalareTime())) {
                hql.append(" and c.declareTime <= :endDeclareTime");
            }
            // 上线的开始时间
            if (StringUtils.isNotBlank(model.getStartOnLineTime())) {
                hql.append(" and c.planOnlineTime >= :startPlanOnlineTime");
            }
            // 上线的开始时间
            if (StringUtils.isNotBlank(model.getEndOnLineTime())) {
                hql.append(" and c.planOnlineTime <= :endPlanOnlineTime");
            }
            // 是否已归并
            if (model.getMergerType() != null) {
                if (model.getMergerType() == 0)
                    hql.append(" and c.unitContentId not in(''))");
                if (model.getMergerType() == 1)
                    hql.append(" and (c.unitContentId = '' or c.unitContentId = null))");
            }
            if (StringUtils.isNotBlank(model.getField())) {
                hql.append(" order by c." + model.getField() + " " + model.getOrderBy());
            } else {
                hql.append(" order by u.id desc,c.id desc");
            }
        }
        Query cq = em.createQuery(hql.toString());
        if (model != null) {
            // 片名
            if (StringUtils.isNotBlank(model.getCpName())) {
                cq.setParameter("cpName", "%" + model.getCpName() + "%");
            }
            // 归并名称
            if (StringUtils.isNotBlank(model.getName())) {
                cq.setParameter("name", "%" + model.getName() + "%");
            }
            // cp的ID
            if (model.getOrgId() != null) {
                hql.append(" and c.orgId = :orgId");
                cq.setParameter("orgId", model.getOrgId());
            }
            // 内容类型
            if (StringUtils.isNotBlank(model.getContentType())) {
                cq.setParameter("contentType", model.getContentType());
            }
            // 是否独家
            if (model.getExclusive() != null) {
                cq.setParameter("exclusive", model.getExclusive());
            }
            // 是否采纳
            if (model.getIsAdopted() != null) {
                cq.setParameter("isAdopted", model.getIsAdopted());
            }
            // 申报的开始时间
            if (StringUtils.isNotBlank(model.getStartDeclareTime())) {
                Date startDeclareTime = DateUtils.stringToDate(model.getStartDeclareTime() + " 00:00:00");
                cq.setParameter("startDeclareTime", startDeclareTime);
            }
            // 申报的结束时间
            if (StringUtils.isNotBlank(model.getEndDecalareTime())) {
                Date endDeclareTime = DateUtils.stringToDate(model.getEndDecalareTime() + " 23:59:59");
                cq.setParameter("endDeclareTime", endDeclareTime);
            }
            // 上线的开始时间
            if (StringUtils.isNotBlank(model.getStartOnLineTime())) {
                Date startPlanOnlineTime = DateUtils.stringToDate(model.getStartOnLineTime() + " 00:00:00");
                cq.setParameter("startPlanOnlineTime", startPlanOnlineTime);
            }
            // 上线的结束时间
            if (StringUtils.isNotBlank(model.getEndOnLineTime())) {
                Date endPlanOnlineTime = DateUtils.stringToDate(model.getEndOnLineTime() + " 23:59:59");
                cq.setParameter("endPlanOnlineTime", endPlanOnlineTime);
            }
        }
        return cq;
    }

}
